﻿// ***********************************************************************
// 程 序 集  : HdhNetQuery
// 建立时间  : 2025-11
// 类库说明  ：Oracle数据库操作类
// 个人网站  ：http://www.hdhcms.com 
// ***********************************************************************
// <copyright>
//     Copyright by He DongHua(何冬华) All, QQ群:876705805 QQ:664627440 Email:664627440@qq.com
// </copyright>
// 如果要使用当前代码，请保存这些信息
// ***********************************************************************
using Microsoft.Extensions.Configuration;
using Oracle.ManagedDataAccess.Client;
using System.Data;
namespace HdhNetQuery
{
    /// HTTP状态码‌2xx‌表示API调用成功。 ‌
    /// 常见成功状态码
    /// ‌200 OK‌：通用成功响应，表示请求已成功处理。 ‌
    /// ‌201 Created‌：表示资源创建成功（如新增数据）。 ‌
    /// ‌204 No Content‌：删除或查询操作成功，但无数据返回。 ‌
    /// 格式说明
    /// 成功响应的数据格式默认为XML，但支持JSON格式（通过请求参数指定）。
    /// API返回失败的HTTP状态码通常为‌4xx或5xx‌。具体错误类型和原因需结合返回的JSON或XML数据中的错误码（如InvalidParameter、InvalidTenant等）进一步分析。 ‌
    /// 
    /// 常见失败场景示例
    /// ‌400错误‌：请求参数格式错误或缺失必要字段。 ‌
    /// ‌401错误‌：未提供有效的身份验证凭证（如API密钥）。 ‌
    /// ‌403错误‌：用户权限不足，无法访问指定资源。 ‌
    /// ‌429错误‌：请求频率过高，超过API订阅限制。 ‌
    /// ‌500错误‌：服务器内部处理异常，需联系服务商。 ‌
    public class HdhcmsOracleHelper : IHdhcmsHelper
    {
        public string? strConn => GetConnString();
        /// <summary>
        /// 返回连接字符串
        /// </summary>
        /// <returns></returns>
        public string? GetConnString()
        {
            //核心依赖包
            //需要安装以下三个关键NuGet包才能使用SetBasePath方法：
            //Microsoft.Extensions.Configuration - 提供配置系统基础功能
            //Microsoft.Extensions.Configuration.FileExtensions - 包含文件相关的配置扩展方法
            //Microsoft.Extensions.Configuration.Json - 支持JSON文件配置读取
            var builder = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json");
            var configuration = builder.Build();
            string? hdhStr = configuration["ConnectionStrings:hdhOracleConnString"];
            return hdhStr;
        }
        /// <summary>
        /// 增、删、改的专用执行方法 
        /// </summary>
        /// <param name="hdhSql">Oracle语句</param>
        /// <param name="param">参数数组 OracleParameter 类型</param>
        /// <returns>操作受影响的行数</returns>
        /// <exception cref="Exception">抛出的异常</exception>
        public int ExecuteNonQuery(string hdhSql, object[]? param = null)
        {
            OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand(hdhSql, conn);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            try
            {
                conn.Open();
                int intCount = cmd.ExecuteNonQuery();
                return intCount;
            }
            catch (Exception ex)
            {
                throw new Exception($"Oracle语句执行错误，语句为：{cmd.CommandText.ToString()}<br>详细错误：{ex.Message}");
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 插入数据并返回最新的自动编号ID
        /// </summary>
        /// <param name="hdhSql">Oracle语句</param>
        /// <param name="param">参数数组 OracleParameter 类型</param>
        /// <returns>返回当前插入数据的自动编号</returns>
        /// <exception cref="Exception">抛出的异常</exception>
        public int InsertDataGetAutoId(string hdhSql, object[]? param = null)
        {
            OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand(hdhSql + ";SELECT LAST_INSERT_ID();", conn);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            try
            {
                conn.Open();
                int intAutoId = (int)cmd.ExecuteScalar();
                return intAutoId;
            }
            catch (Exception ex)
            {
                throw new Exception($"Oracle语句执行错误！语句：{cmd.CommandText.ToString()},详细错误：{ex.Message}");
            }
            finally
            {
                conn.Close();
            }

        }
        /// <summary>
        /// 聚合查询返回单一结果
        /// </summary>
        /// <param name="hdhSql">Oracle语句</param>
        /// <param name="param">参数数组 OracleParameter 类型</param>
        /// <returns>返回当前聚合查询的结果</returns>
        /// <exception cref="Exception">抛出的异常</exception>
        public object ExecuteScalar(string hdhSql, object[]? param = null)
        {
            OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand(hdhSql, conn);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            try
            {
                conn.Open();
                object obj = cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception($"Oracle执行异常，语句：{cmd.CommandText.ToString()}，详细错误：{ex.Message}");
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 获取一个DATATABLE查询结果集
        /// </summary>
        /// <param name="hdhSql">Oracle语句</param>
        /// <param name="param">参数数组 OracleParameter 类型</param>
        /// <returns>返回DataTable对象</returns>
        /// <exception cref="Exception">抛出的异常</exception>
        public DataTable GetDataTable(string hdhSql, object[]? param = null)
        {
            OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand(hdhSql, conn);
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            try
            {
                conn.Open();
                da.Fill(ds);
                DataTable dt = ds.Tables[0];
                return dt;

            }
            catch (Exception ex)
            {
                throw new Exception($"语句执行错误！语句：{cmd.CommandText.ToString()}，详细错误：{ex.Message}");
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 返回DATASET，用0,1,2,3区分不同的DATATABLE
        /// </summary>
        /// <param name="hdhSql"></param>
        /// <param name="param"></param>
        /// <returns>返回DataSet对象</returns>
        /// <exception cref="Exception">抛出的异常</exception>
        public DataSet GetDataSet(string hdhSql, object[]? param = null)
        {
            OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand(hdhSql, conn);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception($"语句执行错！语句：{cmd.CommandText.ToString()}，详细错误：{ex.Message}");
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 返回多个可命名DataTable的DATASET
        /// </summary>
        /// <param name="OracleDic">语句字典，包括名称及语句</param>
        /// <param name="dicParam">参数字典，期中key与语句字典的key一致</param>
        /// <returns>返回DataSet对象</returns>
        /// <exception cref="Exception">抛出的异常</exception>
        public DataSet GetDataSet(Dictionary<string, string> OracleDic, Dictionary<string, object[]>? dicParam = null)
        {
            OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand();
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                foreach (var Oracle in OracleDic)
                {
                    cmd.CommandText = Oracle.Value;
                    if (dicParam != null)
                    {
                        if (dicParam[Oracle.Key] != null)
                        {
                            cmd.Parameters.AddRange(dicParam[Oracle.Key]);
                        }
                    }
                    da.Fill(ds, Oracle.Key);
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception($"语句执行错误！详细错误：{ex.Message}");
            }
            finally { conn.Close(); }
        }
        /// <summary>
        /// 通过事务批量执行Oracle查询语句
        /// </summary>
        /// <param name="sqlList">语句语句集合</param>
        /// <returns>成功返回TRUE，否则FALSE</returns>
        public string ExecuteNonQueryTrans(List<string> sqlList)
        {
            HdhcmsResultEntity hre = new HdhcmsResultEntity();
            OracleConnection conn = new OracleConnection(strConn);
            OracleTransaction trans = conn.BeginTransaction();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.Transaction = trans;
            int count = 0;
            try
            {
                conn.Open();
                foreach (string hdhSql in sqlList)
                {
                    cmd.CommandText = hdhSql;
                    count++;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
                hre.Code = 200;
                hre.Success = true;
                hre.Message = "批量执行Oracle查询成功！";
                hre.DataNumber = count;//执行的语句数量
            }
            catch (OracleException ex)
            {
                trans.Rollback();
                count = 0;
                hre.Code = 400;
                hre.Success = false;
                hre.Message = $"批量执行Oracle语句错误，当前操作的Oracle语句：【{cmd.CommandText}】。详细错误：{ex.Message}";
            }
            finally
            {
                conn.Close();
            }
            string strBack = HdhcmsGenericEntity.EntityToJsonString(hre);
            return strBack;
        }
        /// <summary>
        /// 通过事务批量执行Oracle查询语句，有参数时要全部都有，参数要对等
        /// </summary>
        /// <param name="sqlList"></param>
        /// <param name="paran">参数必填，必须与OracleList形成对应的关系 </param>
        /// <returns></returns>
        public string ExecuteNonQueryTrans(List<string> sqlList, List<object[]>? paran = null)
        {
            string strBack = "";
            HdhcmsResultEntity hre = new HdhcmsResultEntity();
            OracleConnection conn = new OracleConnection(strConn);
            OracleTransaction trans = conn.BeginTransaction();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.Transaction = trans;
            try
            {
                conn.Open();
                for (int i = 0; i < sqlList.Count; i++)
                {
                    string hdhSql = sqlList[i].ToString();
                    cmd.CommandText = hdhSql;
                    if (paran != null && i < paran.Count)
                    {
                        cmd.Parameters.AddRange(paran[i]);
                    }
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
                hre.Message = "批量执行查询成功！";
                hre.DataNumber = sqlList.Count;
            }
            catch (Exception ex)
            {
                hre.Code = 400;
                hre.Success = false;
                hre.DataNumber = 0;
                hre.Message = $"批量查询执行失败！错误的语句：{cmd.CommandText}。详细错误：{ex.Message}";
                trans.Rollback();
            }
            finally
            {
                conn.Close();
            }
            strBack = HdhcmsGenericEntity.EntityToJsonString(hre);
            return strBack;
        }

        /// <summary>
        /// 根据查询结果返回List集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strOracle"></param>
        /// <param name="paran"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public List<T> GetQueryList<T>(string strOracle, object[]? paran = null) where T : new()
        {
            List<T> tList = new List<T>();
            OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand(strOracle, conn);
            if (paran != null)
            {
                cmd.Parameters.AddRange(paran);
            }
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);
                DataTable dt = ds.Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    T t = HdhcmsGenericEntity.DataRowToModel<T>(dr);
                    tList.Add(t);
                }
                return tList;
            }
            catch (Exception ex)
            {
                throw new Exception($"语句执行错误！详细错误：{ex.Message}");
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 根据查询返回Model
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strOracle"></param>
        /// <param name="paran"></param>
        /// <returns>返回Model</returns>
        /// <exception cref="Exception"></exception>
        public T GetQueryModel<T>(string strOracle, object[]? paran = null) where T : new()
        {
            T t = new T();
            OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand(strOracle, conn);
            if (paran != null) { cmd.Parameters.AddRange(paran); }
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);
                DataTable dt = ds.Tables[0];
                DataRow dr = dt.Rows[0];
                t = HdhcmsGenericEntity.DataRowToModel<T>(dr);
            }
            catch (Exception ex)
            {
                throw new Exception($"返回实体结果出错，详细错误：{ex.Message}");
            }
            finally
            {
                conn.Close();
            }
            return t;
        }
        /// <summary>
        /// 查询获取DataReader对象
        /// </summary>
        /// <param name="hdhSql">Oracle语句</param>
        /// <param name="param">参数数组 OracleParameter 类型</param>
        /// <returns>返回一个DataReader对象</returns>
        /// <exception cref="Exception">抛出的异常</exception>
        public object GetExecuteReader(string hdhSql, object[]? param = null)
        {
            OracleConnection conn = new OracleConnection(strConn);
            OracleCommand cmd = new OracleCommand(hdhSql, conn);
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            try
            {
                conn.Open();
                OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//调用关闭OracleDataReader的同时关闭conn连接
                return reader;
            }
            catch (Exception ex)
            {
                throw new Exception($"Oracle执行错误！语句：{cmd.CommandText.ToString()}，详细错误：{ex.Message}");
            }
        }
        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public bool TableExists(string TableName)
        {
            bool boolBack;
            //string hdhSql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            //string hdhSql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'TableName'";
            string hdhSql = $"SHOW TABLES LIKE '{TableName}'";
            object obj = ExecuteScalar(hdhSql);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                boolBack = false;
            }
            else
            {
                boolBack = true;
            }
            return boolBack;
        }
        /// <summary>
        /// 表的字段是否存在
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="FieldName">字段表</param>
        /// <returns></returns>
        public bool TableFieldExists(string TableName,string FieldName)
        {
            bool boolBack;
            string hdhSql = @$"SELECT EXISTS (
                SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE  TABLE_NAME = '{TableName}'
                  AND COLUMN_NAME = '{FieldName}'
            );";
            object obj = ExecuteScalar(hdhSql);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                boolBack = false;
            }
            else
            {
                boolBack = Convert.ToBoolean(obj);
            }
            return boolBack;
        }
    }
}
